# python
from fastapi import FastAPI, Request, HTTPException
from fastapi.responses import HTMLResponse, FileResponse
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
import os
import json
import sqlite3
from typing import List, Dict, Any, Tuple
from openpyxl import Workbook
import re

app = FastAPI()

# 静态资源与模板
app.mount(
    "/videos",
    StaticFiles(directory=r"./videos"),
    name="videos",
)  # 别改
templates = Jinja2Templates(directory="templates")  # 别改

# 路径配置
VIDEO_DIR = r"./videos"  # 别改，把视频放在这里面就行
DB_FILE = "dbs/test1.db"  # 可以改
PROGRESS_FILE = "category.json"  # 分类json
JSON_DIR = "json"  # 分类定义目录，别改
EXPORT_DIR = "exports"  # 导出目录，别改
EXPORT_FILE = os.path.join(EXPORT_DIR, "videos.xlsx")  #  导出文件名字，可以改


# ============ 工具与校验 ============

def ensure_dirs():
    os.makedirs(os.path.dirname(DB_FILE), exist_ok=True)
    os.makedirs(JSON_DIR, exist_ok=True)
    os.makedirs(EXPORT_DIR, exist_ok=True)


def get_conn():
    return sqlite3.connect(DB_FILE)


def extract_video_id(filename: str) -> str:
    # 取文件名前缀作为 video_id
    return os.path.splitext(filename)[0]


def validate_category_schema(obj: Any) -> bool:
    # 严格校验：
    # 根：必须且仅包含 {value(str), label(str), children(list[子项])}
    # 子：必须且仅包含 {value(str), label(str)}
    if not isinstance(obj, dict):
        return False
    root_keys = set(obj.keys())
    if root_keys != {"value", "label", "children"}:
        return False
    if not isinstance(obj["value"], str) or not isinstance(obj["label"], str):
        return False
    if not isinstance(obj["children"], list) or len(obj["children"]) == 0:
        return False
    for child in obj["children"]:
        if not isinstance(child, dict):
            return False
        if set(child.keys()) != {"value", "label"}:
            return False
        if not isinstance(child["value"], str) or not isinstance(child["label"], str):
            return False
    return True


def load_categories_from_json_dir() -> List[Dict[str, Any]]:
    """
    从 json 目录加载全部分类定义，只有严格符合规范的才会被返回。
    支持文件顶层为单个对象或对象数组。
    """
    if not os.path.exists(JSON_DIR):
        return []
    categories: List[Dict[str, Any]] = []
    for fname in os.listdir(JSON_DIR):
        if not fname.lower().endswith(".json"):
            continue
        fpath = os.path.join(JSON_DIR, fname)
        try:
            with open(fpath, "r", encoding="utf-8") as f:
                data = json.load(f)
            if isinstance(data, dict):
                if validate_category_schema(data):
                    categories.append(data)
            elif isinstance(data, list):
                ok_any = False
                for item in data:
                    if validate_category_schema(item):
                        categories.append(item)
                        ok_any = True
                if not ok_any:
                    print(f"分类文件不符合规范(列表无合法项): {fpath}")
            else:
                print(f"分类文件不符合规范(顶层必须为对象或数组): {fpath}")
        except Exception as e:
            print(f"读取分类文件失败: {fpath}, 错误: {e}")
    return categories


def categories_contains(super_cat: str, sub_cat: str, cats: List[Dict[str, Any]]) -> bool:
    for root in cats:
        if root.get("value") == super_cat:
            for child in root.get("children", []):
                if child.get("value") == sub_cat:
                    return True
    return False


# ============ 时间工具 ============

_TIME_RE = re.compile(r"^\d{2}:\d{2}:\d{2}$")


def time_str_to_seconds(s: str) -> int:
    if not isinstance(s, str) or not _TIME_RE.match(s):
        raise ValueError("时间格式必须为 HH:MM:SS")
    h, m, sec = map(int, s.split(":"))
    return h * 3600 + m * 60 + sec


# ============ 数据库初始化/迁移 ============

def init_database():
    ensure_dirs()
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS videos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            video_id TEXT,
            filename TEXT UNIQUE NOT NULL,
            super_category TEXT DEFAULT '',
            sub_category TEXT DEFAULT '',
            status INTEGER DEFAULT 0,  -- 0: 未处理, 1: 标记(满足), -1: 拒绝(不满足)
            start_time TEXT DEFAULT '',
            end_time TEXT DEFAULT '',
            segment_content TEXT DEFAULT ''
        )
        """
    )
    conn.commit()
    migrate_database(conn)
    conn.close()


def migrate_database(conn: sqlite3.Connection):
    cursor = conn.cursor()
    cursor.execute("PRAGMA table_info(videos)")
    cols = {row[1] for row in cursor.fetchall()}
    # 旧库补列
    if "video_id" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN video_id TEXT")
    if "super_category" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN super_category TEXT DEFAULT ''")
    if "sub_category" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN sub_category TEXT DEFAULT ''")
    if "status" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN status INTEGER DEFAULT 0")
    if "start_time" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN start_time TEXT DEFAULT ''")
    if "end_time" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN end_time TEXT DEFAULT ''")
    if "segment_content" not in cols:
        cursor.execute("ALTER TABLE videos ADD COLUMN segment_content TEXT DEFAULT ''")
    conn.commit()
    # 索引
    cursor.execute("CREATE INDEX IF NOT EXISTS idx_videos_video_id ON videos(video_id)")
    conn.commit()


# ============ 扫描与同步 ============

def scan_and_update_videos():
    if not os.path.exists(VIDEO_DIR):
        return

    conn = get_conn()
    cursor = conn.cursor()

    try:
        cursor.execute("SELECT filename FROM videos")
        existing_files = set(row[0] for row in cursor.fetchall())

        video_files = set(
            f for f in os.listdir(VIDEO_DIR) if f.lower().endswith((".mp4", ".avi", ".mov"))
        )

        new_files = video_files - existing_files
        for filename in new_files:
            vid = extract_video_id(filename)
            cursor.execute(
                "INSERT OR IGNORE INTO videos (video_id, filename, super_category, sub_category, status) VALUES (?, ?, ?, ?, ?)",
                (vid, filename, "", "", 0),
            )

        deleted_files = existing_files - video_files
        for filename in deleted_files:
            cursor.execute("DELETE FROM videos WHERE filename = ?", (filename,))

        conn.commit()
        print(f"数据库已更新: 新增 {len(new_files)} 个文件, 删除 {len(deleted_files)} 个文件")

    except Exception as e:
        print(f"扫描和更新视频时出错: {e}")
    finally:
        conn.close()


# ============ 查询与统计 ============

def get_all_videos() -> List[Tuple]:
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        """
        SELECT
            id, video_id, filename, super_category, sub_category,
            status, start_time, end_time, segment_content
        FROM videos
        ORDER BY id
        """
    )
    videos = cursor.fetchall()
    conn.close()
    return videos


def get_unprocessed_videos() -> List[Tuple]:
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute(
        """
        SELECT
            id, video_id, filename, super_category, sub_category,
            status, start_time, end_time, segment_content
        FROM videos
        WHERE status = 0
        ORDER BY id
        """
    )
    videos = cursor.fetchall()
    conn.close()
    return videos


def mark_video_with_category(video_identifier: Dict[str, Any]) -> None:
    """
    支持用 video_id 或 filename 标记；同时写入 super_category/sub_category、
    start_time/end_time/segment_content，status=1
    video_identifier: {
      "video_id"?: str,
      "filename"?: str,
      "super_category": str,
      "sub_category": str,
      "start_time": str,
      "end_time": str,
      "segment_content": str
    }
    """
    video_id = video_identifier.get("video_id")
    filename = video_identifier.get("filename")
    super_cat = video_identifier.get("super_category", "")
    sub_cat = video_identifier.get("sub_category", "")
    start_time = video_identifier.get("start_time", "")
    end_time = video_identifier.get("end_time", "")
    segment_content = video_identifier.get("segment_content", "")

    if not super_cat or not sub_cat:
        raise ValueError("缺少大类或子类")

    conn = get_conn()
    cursor = conn.cursor()
    try:
        if video_id:
            cursor.execute(
                """
                UPDATE videos
                SET super_category = ?, sub_category = ?, start_time = ?, end_time = ?, segment_content = ?, status = 1
                WHERE video_id = ?
                """,
                (super_cat, sub_cat, start_time, end_time, segment_content, video_id),
            )
        elif filename:
            cursor.execute(
                """
                UPDATE videos
                SET super_category = ?, sub_category = ?, start_time = ?, end_time = ?, segment_content = ?, status = 1
                WHERE filename = ?
                """,
                (super_cat, sub_cat, start_time, end_time, segment_content, filename),
            )
        else:
            raise ValueError("缺少 video_id 或 filename")

        conn.commit()
    finally:
        conn.close()


def reject_video(filename: str):
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("UPDATE videos SET status = -1 WHERE filename = ? AND status = 0", (filename,))
    conn.commit()
    conn.close()


def get_statistics() -> Dict[str, int]:
    conn = get_conn()
    cursor = conn.cursor()
    cursor.execute("SELECT COUNT(*) FROM videos WHERE status = 1")
    satisfied_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM videos WHERE status = -1")
    unsatisfied_count = cursor.fetchone()[0]
    cursor.execute("SELECT COUNT(*) FROM videos WHERE status = 0")
    unmarked_count = cursor.fetchone()[0]
    conn.close()
    return {
        "satisfied": satisfied_count,
        "unsatisfied": unsatisfied_count,
        "unmarked": unmarked_count,
    }


def get_progress():
    if not os.path.exists(PROGRESS_FILE):
        return {"last_index": 0}
    try:
        with open(PROGRESS_FILE, "r", encoding="utf-8") as f:
            return json.load(f)
    except:
        return {"last_index": 0}


def save_progress(index: int):
    with open(PROGRESS_FILE, "w", encoding="utf-8") as f:
        json.dump({"last_index": index}, f, ensure_ascii=False)


def find_first_unprocessed_video() -> int:
    videos = get_all_videos()
    for i, row in enumerate(videos):
        status = row[5]  # 按选择列顺序: status 在索引5
        if status == 0:
            return i
    return 0 if videos else 0


# ============ 导出 ============

def export_to_excel(file_path: str) -> str:
    ensure_dirs()
    rows = get_all_videos()
    wb = Workbook()
    ws = wb.active
    ws.title = "videos"
    headers = [
        "id", "video_id", "filename", "super_category", "sub_category",
        "status", "start_time", "end_time", "segment_content"
    ]
    ws.append(headers)
    for row in rows:
        ws.append(list(row))
    wb.save(file_path)
    return file_path


# ============ 路由 ============

@app.get("/", response_class=HTMLResponse)
async def read_root(request: Request):
    return templates.TemplateResponse("index.html", {"request": request})


@app.get("/api/categories")
async def get_categories():
    """
    返回严格校验通过的多分类级联数据。
    如未按规范，将返回空数组。
    """
    cats = load_categories_from_json_dir()
    return {"categories": cats}


@app.get("/api/videos")
async def video_list():
    # 每次请求都扫描并更新视频
    scan_and_update_videos()

    videos = get_all_videos()
    video_list = [
        {
            "video_id": row[1],
            "filename": row[2],
            "super_category": row[3],
            "sub_category": row[4],
            "status": row[5],
            "start_time": row[6],
            "end_time": row[7],
            "segment_content": row[8],
        }
        for row in videos
    ]

    marked_list = [v["filename"] for v in video_list if v["status"] == 1]
    rejected_list = [v["filename"] for v in video_list if v["status"] == -1]

    progress = get_progress()
    first_unprocessed_index = find_first_unprocessed_video()
    if progress.get("last_index") != first_unprocessed_index:
        progress["last_index"] = first_unprocessed_index
        save_progress(first_unprocessed_index)

    statistics = get_statistics()

    return {
        "videos": video_list,
        "marked": marked_list,
        "rejected": rejected_list,
        "progress": progress,
        "statistics": statistics,
    }


@app.post("/api/mark")
async def mark_video_endpoint(data: Dict[str, Any]):
    """
    data = {
      "video_id"?: str,
      "filename"?: str,
      "super_category": str,
      "sub_category": str,
      "index"?: int,
      "start_time": str,        # HH:MM:SS
      "end_time": str,          # HH:MM:SS
      "segment_content": str
    }
    """
    super_cat = data.get("super_category", "")
    sub_cat = data.get("sub_category", "")
    start_time = data.get("start_time", "") or ""
    end_time = data.get("end_time", "") or ""
    segment_content = (data.get("segment_content", "") or "").strip()

    # 校验分类是否存在且规范
    cats = load_categories_from_json_dir()
    if not cats:
        raise HTTPException(status_code=400, detail="分类未配置或不符合规范")
    if not (super_cat and sub_cat and categories_contains(super_cat, sub_cat, cats)):
        raise HTTPException(status_code=400, detail="非法的分类标签")

    # 时间与内容校验（与前端一致）
    if not start_time or not end_time or not segment_content:
        raise HTTPException(status_code=400, detail="请填写起始时间、终止时间和片段内容")
    if start_time == "00:00:00" and end_time == "00:00:00":
        raise HTTPException(status_code=400, detail="起始时间和终止时间不能都为 00:00:00")
    try:
        s_sec = time_str_to_seconds(start_time)
        e_sec = time_str_to_seconds(end_time)
    except ValueError as ve:
        raise HTTPException(status_code=400, detail=str(ve))
    if s_sec > e_sec:
        raise HTTPException(status_code=400, detail="起始时间不能大于终止时间")

    try:
        mark_video_with_category({
            "video_id": data.get("video_id"),
            "filename": data.get("filename"),
            "super_category": super_cat,
            "sub_category": sub_cat,
            "start_time": start_time,
            "end_time": end_time,
            "segment_content": segment_content
        })
    except ValueError as ve:
        raise HTTPException(status_code=400, detail=str(ve))

    # 更新进度
    first_unprocessed_index = find_first_unprocessed_video()
    save_progress(first_unprocessed_index)

    statistics = get_statistics()
    return {"status": "success", "next_index": first_unprocessed_index, "statistics": statistics}


@app.post("/api/reject")
async def reject_video_endpoint(data: Dict[str, Any]):
    filename = data.get("filename")
    if not filename:
        raise HTTPException(status_code=400, detail="缺少 filename")
    reject_video(filename)

    first_unprocessed_index = find_first_unprocessed_video()
    save_progress(first_unprocessed_index)

    statistics = get_statistics()
    return {"status": "success", "next_index": first_unprocessed_index, "statistics": statistics}


@app.post("/api/force-update-progress")
async def force_update_progress():
    first_unprocessed_index = find_first_unprocessed_video()
    save_progress(first_unprocessed_index)
    statistics = get_statistics()
    return {"status": "success", "new_index": first_unprocessed_index, "statistics": statistics}


@app.post("/api/update-database")
async def update_database():
    scan_and_update_videos()
    statistics = get_statistics()
    return {"status": "success", "statistics": statistics}


@app.get("/api/export")
async def export_excel():
    """
    所有视频标记完毕(无 status=0)才允许导出。
    导出为 Excel 并返回下载。
    """
    stats = get_statistics()
    if stats.get("unmarked", 1) != 0:
        raise HTTPException(status_code=400, detail="未标记完毕，禁止导出")

    path = export_to_excel(EXPORT_FILE)
    if not os.path.exists(path):
        raise HTTPException(status_code=500, detail="导出失败")

    return FileResponse(
        path,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        filename="videos.xlsx",
    )


# ============ 启动 ============

if __name__ == "__main__":
    init_database()
    scan_and_update_videos()
    import uvicorn

    uvicorn.run(app, host="127.0.0.1", port=8000)